﻿namespace CRM.DataAccess.DAOs
{
    using System.Collections.Generic;
    using System.Data.Entity;
    using CRM.DataAccess.Entities;
    using System.Linq;
    using System;

    public class OrganizationDAO : DbContext
    {
        public OrganizationDAO()
            : base("name=Entities")
        {
        }
        public DbSet<ORGANIZATION> ORGANIZATIONS { get; set; }
        public DbSet<ORGANIZATION_DETAIL> ORGANIZATION_DETAIL { get; set; }
        public DbSet<ORGANIZATION_INDUSTRY> ORGANIZATION_INDUSTRY { get; set; }
        public DbSet<ORGANIZATION_RATING> ORGANIZATION_RATING { get; set; }
        public DbSet<ORGANIZATION_TYPE> ORGANIZATION_TYPE { get; set; }
        public DbSet<ORGANIZATION_NOT_SEND_INVOICE_QUOTE> ORGANIZATION_NOT_SEND_INVOICE_QUOTE { get; set; }
        public DbSet<USER> USERS { get; set; }

        //Get Org Type
        public List<ORGANIZATION_TYPE> GetOrganizationType()
        {
            try
            {
                var Query = from org in ORGANIZATION_TYPE
                            select org;
                List<ORGANIZATION_TYPE> orgs = Query.ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Update Organization
        public int UpdateOrganization(ORGANIZATION org)
        {
            try
            {
                var original = ORGANIZATIONS.Find(org.ID);
                if (original != null)
                {
                    Entry(original).CurrentValues.SetValues(org);
                    SaveChanges();
                    return 1;
                }
                return -1;
            }
            catch
            {
                return -1;
            }
        }
        // Get User by ID
        public USER GetUserByID(long id)
        {
            try
            {
                var Query = from u in USERS
                            where u.ID == id
                            select u;
                List<USER> us = Query.ToList();
                if (us != null)
                    return us[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Organization Detail By ID
        public ORGANIZATION_DETAIL GetOrganizationDetailByID(long id)
        {
            try
            {
                var Query = from o in ORGANIZATION_DETAIL
                            where o.OrgID == id
                            select o;
                List<ORGANIZATION_DETAIL> orgs = Query.AsNoTracking().ToList();
                if (orgs != null)
                    return orgs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Organization by OrgName
        public ORGANIZATION GetOrganizationByOrgName(string OrgName)
        {
            try
            {
                var Query = from o in ORGANIZATIONS
                            where o.ORGANIZATION_NAME.ToUpper() == OrgName.ToUpper()
                            select o;
                List<ORGANIZATION> orgs = Query.ToList();
                if (orgs != null)
                    return orgs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Organization by OrgID
        public ORGANIZATION GetOrganizationByID(long id)
        {
            try
            {
                var Query = from o in ORGANIZATIONS
                            where o.ID == id
                            select o;
                List<ORGANIZATION> orgs = Query.ToList();
                if (orgs != null)
                    return orgs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Rating by OrgID
        public ORGANIZATION_RATING GetRatingByID(long id)
        {
            try
            {
                var Query = from r in ORGANIZATION_RATING
                            where r.ID == id
                            select r;
                List<ORGANIZATION_RATING> rs = Query.ToList();
                if (rs != null)
                    return rs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //add new Rating
        public int AddNewRating(ORGANIZATION_RATING or)
        {
            try
            {
                this.ORGANIZATION_RATING.Add(or);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Get Rating by Name
        public ORGANIZATION_RATING GetRatingByName(string name)
        {
            try
            {
                var Query = from r in ORGANIZATION_RATING
                            where r.ORGANIZATION_RATING_NAME.ToUpper() == name.ToUpper()
                            select r;
                List<ORGANIZATION_RATING> ts = Query.ToList();
                if (ts != null)
                    return ts[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //add new Type
        public int AddNewType(ORGANIZATION_TYPE ot)
        {
            try
            {
                this.ORGANIZATION_TYPE.Add(ot);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Get Type by Name
        public ORGANIZATION_TYPE GetTypeByName(string name)
        {
            try
            {
                var Query = from t in ORGANIZATION_TYPE
                            where t.ORGANIZATION_TYPE_NAME.ToUpper() == name.ToUpper()
                            select t;
                List<ORGANIZATION_TYPE> ts = Query.ToList();
                if (ts != null)
                    return ts[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Type by OrgID
        public ORGANIZATION_TYPE GetTypeByID(long id)
        {
            try
            {
                var Query = from t in ORGANIZATION_TYPE
                            where t.ID == id
                            select t;
                List<ORGANIZATION_TYPE> ts = Query.ToList();
                if (ts != null)
                    return ts[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Industry by Name
        public ORGANIZATION_INDUSTRY GetIndustryByName(string name)
        {
            try
            {
                var Query = from ind in ORGANIZATION_INDUSTRY
                            where ind.ORGANIZATION_INDUSTRY_NAME.ToUpper() == name.ToUpper()
                            select ind;
                List<ORGANIZATION_INDUSTRY> inds = Query.ToList();
                if (inds != null)
                    return inds[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Industry by ID
        public ORGANIZATION_INDUSTRY GetIndustryByID(long id)
        {
            try
            {
                var Query = from ind in ORGANIZATION_INDUSTRY
                            where ind.ID == id
                            select ind;
                List<ORGANIZATION_INDUSTRY> inds = Query.ToList();
                if (inds != null)
                    return inds[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Org Rating
        public List<ORGANIZATION_RATING> GetOrganizationRating()
        {
            try
            {
                var Query = from org in ORGANIZATION_RATING
                            select org;
                List<ORGANIZATION_RATING> orgs = Query.ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Org Industry
        public List<ORGANIZATION_INDUSTRY> GetOrganizationIndustry()
        {
            try
            {
                var Query = from org in ORGANIZATION_INDUSTRY
                            select org;
                List<ORGANIZATION_INDUSTRY> orgs = Query.ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Check exist
        public int CheckExist(string orgname)
        {
            try
            {
                var Query = from org in ORGANIZATIONS
                            where org.ORGANIZATION_NAME.ToUpper() == orgname.ToUpper()
                            select org;
                List<ORGANIZATION> orgs = Query.ToList();
                if (orgs.Count > 0)
                    return 1;
                return 0;
            }
            catch
            {
                return -1;
            }
        }
        //Get Org not send invoices and quotation by condition
        public List<ORGANIZATION_NOT_SEND_INVOICE_QUOTE> GetOrgNotSendInvoiceAndQuoteByCondition(long? userID, string orgname, string contactnumber, string email)
        {
            try
            {
                var Query = from org in ORGANIZATION_NOT_SEND_INVOICE_QUOTE
                            where org.UserID == (userID == null ? org.UserID : userID)
                            && (orgname != "" ? (org.ORGANIZATION_NAME.ToUpper().Contains(orgname.ToUpper())) : (true))
                            && (contactnumber != "" ? (org.CONTACT_NUMBER.ToUpper().Contains(contactnumber.ToUpper())) : (true))
                            && (email != "" ? (org.PRIMARY_EMAIL.ToUpper().Contains(email.ToUpper())) : (true))
                          //      && org.ORGANIZATION_NAME.ToUpper() == (orgname == "" ? org.ORGANIZATION_NAME.ToUpper() : orgname.ToUpper())
                          //      && org.CONTACT_NUMBER.ToUpper() == (contactnumber == "" ? org.CONTACT_NUMBER.ToUpper() : contactnumber.ToUpper())
                          //      && org.PRIMARY_EMAIL.ToUpper() == (email == "" ? org.PRIMARY_EMAIL.ToUpper() : email.ToUpper())
                            select org;
                List<ORGANIZATION_NOT_SEND_INVOICE_QUOTE> orgs = Query.ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Org & User by condition
        public List<ORGANIZATION_DETAIL> GetOrganizationByCondition(DateTime? dateFrom, DateTime? dateTo, long? assignedToID, string orgname, string contactnumber, string primaryemail)
        {
            try
            {
                var Query = from org in ORGANIZATION_DETAIL
                            where org.ASSIGN_TO_ID == (assignedToID == null ? org.ASSIGN_TO_ID : assignedToID)
                            && (orgname != "" ? (org.ORGANIZATION_NAME.ToUpper().Contains(orgname.ToUpper())) : (true))
                            && (contactnumber != "" ? (org.CONTACT_NUMBER.ToUpper().Contains(contactnumber.ToUpper())) : (true))
                            && (primaryemail != "" ? (org.PRIMARY_EMAIL.ToUpper().Contains(primaryemail.ToUpper())) : (true))
                                    //&& org.ORGANIZATION_NAME.ToUpper() == (orgname == "" ? org.ORGANIZATION_NAME.ToUpper() : orgname.ToUpper())
                                    //&& org.CONTACT_NUMBER.ToUpper() == (contactnumber == "" ? org.CONTACT_NUMBER.ToUpper() : contactnumber.ToUpper())
                                    //&& org.PRIMARY_EMAIL.ToUpper() == (primaryemail == "" ? org.PRIMARY_EMAIL.ToUpper() : primaryemail.ToUpper())
                                    && (org.DATE_ENTERED >= dateFrom && org.DATE_ENTERED < dateTo)
                            select org;
                List<ORGANIZATION_DETAIL> orgs = Query.AsNoTracking().ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Org distinc by condition
        public List<ORGANIZATION> GetCompanyByCondition(DateTime? dateFrom, DateTime? dateTo, long? assignedToID, string orgname, string contactnumber, string primaryemail)
        {
            try
            {
                var Query = (from org in ORGANIZATIONS
                             where org.ASSIGN_TO == (assignedToID == null ? org.ASSIGN_TO : assignedToID)
                                 && (orgname != "" ? (org.ORGANIZATION_NAME.ToUpper().Contains(orgname.ToUpper())) : (true))
                                 && (contactnumber != "" ? (org.CONTACT_NUMBER.ToUpper().Contains(contactnumber.ToUpper())) : (true))
                                 && (primaryemail != "" ? (org.PRIMARY_EMAIL.ToUpper().Contains(primaryemail.ToUpper())) : (true))
                
                            //        && org.PRIMARY_EMAIL.ToUpper().Contains(primaryemail == "" ? org.PRIMARY_EMAIL.ToUpper() : primaryemail.ToUpper())
                           //           && org.ORGANIZATION_NAME.ToUpper() == (orgname == "" ? org.ORGANIZATION_NAME.ToUpper() : orgname.ToUpper())
                            //          && org.CONTACT_NUMBER.ToUpper() == (contactnumber == "" ? org.CONTACT_NUMBER.ToUpper() : contactnumber.ToUpper())
                             //         && org.PRIMARY_EMAIL.ToUpper() == (primaryemail == "" ? org.PRIMARY_EMAIL.ToUpper() : primaryemail.ToUpper())
                                 && (org.DATE_ENTERED >= dateFrom && org.DATE_ENTERED < dateTo)
                             select org);
                List<ORGANIZATION> orgs = Query.AsNoTracking().ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Delete Organization
        public int DeleteOrganization(long orgID)
        {
            try
            {
                ORGANIZATIONS.RemoveRange(ORGANIZATIONS.Where(x => x.ID == orgID));
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Add new Industry
        public int AddNewIndustry(ORGANIZATION_INDUSTRY oi)
        {
            try
            {
                this.ORGANIZATION_INDUSTRY.Add(oi);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //Add new Organization
        public int AddNewOrganization(ORGANIZATION org)
        {
            try
            {
                this.ORGANIZATIONS.Add(org);
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        // Add List Org from excel file 
        public int AddListOrgFromExcelFile(List<ORGANIZATION> Orgs)
        {
             var trans = this.Database.BeginTransaction();
             try
             {
                 foreach (ORGANIZATION org in Orgs)
                 {
                     this.ORGANIZATIONS.Add(org);
                     this.SaveChanges();
                 }
                 trans.Commit();
                 return 1;
             }
             catch
             {
                 trans.Rollback();
                 return -1;
             }
        }
        // get all Org 
        public List<ORGANIZATION> ListAll(long? assignedToID)
        {
            try
            {
                var userQuery = from u in ORGANIZATIONS
                                where !u.DELETED
                                && u.ASSIGN_TO == (assignedToID == null ? u.ASSIGN_TO : assignedToID)
                                select u;
                List<ORGANIZATION> orgs = userQuery.ToList();
                if (orgs.Count > 0)
                    return orgs;
                return null;
            }
            catch
            {
                return null;
            }
        }

    }
}
